using System;
using System.Data;
using System.Data.Sql;
using System.Data.SqlClient;
using System.Collections.Generic;
using System.Text;

namespace StudentManagementApplication.DAO
{
    public class RollcallDAO : BaseDAO
    {
        public RollcallDAO()
        {
        }

        public Entities.Rollcall CreateRollcallFromReader(IDataReader reader)
        {
            Entities.Rollcall rollcall = new Entities.Rollcall();
            rollcall.StudentID = (string)reader["cStudentID"];
            rollcall.RollcallDay = ((DateTime)reader["dRollcallDay"]).Date;
            rollcall.Attend = (int)reader["iAttend"];
            return rollcall;
        }

        public void InsertRollcall(Entities.Rollcall rollcall)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcInsertRollcall", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", rollcall.StudentID));
                command.Parameters.Add(new SqlParameter("@dRollcallDay", rollcall.RollcallDay));
                command.Parameters.Add(new SqlParameter("@iAttend", rollcall.Attend));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)

            }
        }

        public void UpdateRollcall(Entities.Rollcall rollcall)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcUpdateRollcall", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", rollcall.StudentID));
                command.Parameters.Add(new SqlParameter("@dRollcallDay", rollcall.RollcallDay));
                command.Parameters.Add(new SqlParameter("@iAttend", rollcall.Attend));
                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeleteRollcall(Entities.Rollcall rollcall)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteRollcall", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", rollcall.StudentID));
                command.Parameters.Add(new SqlParameter("@dRollcallDay", rollcall.RollcallDay));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }

        public void DeleteRollcallByStudentID(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcDeleteRollcallByStudentID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("@cStudentID", sStudentID));

                connection.Open();
                command.ExecuteNonQuery();
                //if (command.ExecuteNonQuery() <= 0)
                //    throw;
            }
        }


        public List<Entities.Rollcall> GetRollcallByRollcallDay(DateTime dRollcallDay)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallByRollcallDay", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("dRollcallDay", dRollcallDay));

                connection.Open();
                List<Entities.Rollcall> rollCalls = new List<Entities.Rollcall>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while( reader.Read())
                    {
                        rollCalls.Add(CreateRollcallFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return rollCalls;
            }
        }

        public List<Entities.Rollcall> GetRollcallByStudentID1(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallsByStudentID1", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cStudentID", sStudentID));

                connection.Open();
                List<Entities.Rollcall> rollCalls = new List<Entities.Rollcall>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        rollCalls.Add(CreateRollcallFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return rollCalls;
            }
        }

        public Entities.Rollcall GetRollcallByStudentIDAndRollcallDay(string sStudentID,DateTime dRollcallDay)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallByStudentIDAndRollcallDay", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cStudentID", sStudentID));
                command.Parameters.Add(new SqlParameter("dRollcallDay", dRollcallDay));

                connection.Open();
                Entities.Rollcall rollcall = new Entities.Rollcall();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    if(reader.Read())
                    {
                        rollcall = CreateRollcallFromReader(reader);
                    }
                    //else
                    //    throw;
                }
                return rollcall;
            }
        }

        public List<Entities.Rollcall> GetAllRollcalls()
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetAllRollcalls", connection);
                command.CommandType = CommandType.StoredProcedure;

                connection.Open();
                List<Entities.Rollcall> rollCalls = new List<Entities.Rollcall>();
                using (IDataReader reader = command.ExecuteReader(CommandBehavior.CloseConnection))
                {
                    while (reader.Read())
                    {
                        rollCalls.Add(CreateRollcallFromReader(reader));
                    }
                    //else
                    //    throw;
                }
                return rollCalls;
            }
        }

        public DataTable GetRollcallByStudentID(string sStudentID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallsByStudentID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cStudentID", sStudentID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetRollcallsByRollcallDay(DateTime dRollcallDay)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallsByRollcallDay", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("dRollcallDay", dRollcallDay));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetRollcallsByCourseID(string sCourseID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallsByCourseID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cCourseID", sCourseID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }

        public DataTable GetRollcallsByClassID(string sClassID)
        {
            using (SqlConnection connection = GetConnection())
            {
                SqlCommand command = new SqlCommand("prcGetRollcallsByClassID", connection);
                command.CommandType = CommandType.StoredProcedure;

                command.Parameters.Add(new SqlParameter("cClassID", sClassID));

                connection.Open();
                DataTable dataTable = new DataTable();

                using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
                {
                    dataAdapter.Fill(dataTable);
                }

                return dataTable;
            }
        }
    }
}
